# -*- coding: utf-8 -*-
"""
Created on Mon Jun 14 20:33:45 2021
@author: 农产品分析师
"""
import sys
import socket
sys.path.append("../")
import pymysql


class TjdData():
    def __init__(self, db_name):
        self.host = "192.168.3.179"
        self.port = 3306
        self.user = 'hf_user'
        self.db = db_name
        self.password = "290202"
        self.charset = "utf8"

    def connect(self):
        self.conn = pymysql.connect(host=self.host, port=self.port,
                                    user=self.user, db=self.db,
                                    password=self.password, charset=self.charset)

    def execute_and_disconnect(self):
        self.conn.commit()
        self.conn.close()

    def exec_sql(self, sql, value=()):
        cur = self.conn.cursor()
        cur.execute(sql, value)
        res = cur.fetchall()
        cur.close()
        return res

    def exec_sql_many(self, sql, value):  # value是列表嵌套
        self.conn.ping(reconnect=True)
        cur = self.conn.cursor()
        cur.executemany(sql, value)
        res = cur.fetchall()
        cur.close()
        return res

    def copy_a_new_table(self, sheet_name):
        cur = self.conn.cursor()
        sql = f"CREATE TABLE if not exists {sheet_name} LIKE task_all.main_table"
        print(f"execute>> {sql}")
        cur.execute(sql)
        res = cur.fetchall()
        cur.close()
        self.execute_and_disconnect()
        return res

    def drop_table(self, sheet_name):
        cur = self.conn.cursor()
        sql = f"delete from {sheet_name} where id < 100000000"
        print(f"execute>> {sql}")
        cur.execute(sql)
        res = cur.fetchall()
        cur.close()
        self.conn.commit()
        return res

    def get_all_tables(self):
        cur = self.conn.cursor()
        sql = "show tables"
        print(f"execute>> {sql}")
        cur.execute(sql)
        res = cur.fetchall()
        res = list(res)
        res = [s[0] for s in res]
        res_u = [s.upper() for s in res]
        res_l = [s.lower() for s in res]
        return res + res_u + res_l


def upload_task(values=None, username="hst"):
    assert username in ['wuw', 'hst', 'zhq', 'yqt', 'wcy', 'zzh']
    db = TjdData("task_all")
    db.connect()
    all_tables = db.get_all_tables()
    table_name = username
    if table_name in all_tables:
        sql_look = "select * from %s" % table_name
        all_published = db.exec_sql(sql_look)
        if len(all_published) == 0:
            id_start = 0
        else:
            id_start = max([int(s[0]) for s in all_published]) + 1
    else:
        db.copy_a_new_table(table_name)
        id_start = 0
    new_values = []
    ids = []
    for v in values:
        new_v = [id_start, *v]
        new_values.append(new_v)
        ids.append(id_start)
        id_start += 1
    sql = "insert into `" + table_name + "` values (%s, %s, %s, %s, %s, %s, %s, %s)"

    db.exec_sql_many(sql, new_values)
    db.conn.commit()
    return ids


def find_a_task(username="wuw"):
    assert username in ['wuw', 'hst', 'zhq', 'yqt', 'wcy', 'zzh']
    db = TjdData("task_all")
    db.connect()
    table_name = username
    sql = f"select * from {table_name} where run = 0"
    re = db.exec_sql(sql)
    choose_ids = [s[0] for s in re]
    print(choose_ids)
    choose_id = min(choose_ids)
    sql_update = f"update {table_name} set run = 1 where id = {choose_id}"
    db.exec_sql(sql_update)
    db.conn.commit()
    re = db.exec_sql(sql)
    hostname = socket.gethostname()
    ip = socket.gethostbyname(hostname)
    sql_ip = f"update {table_name} set ip = '{ip}' where id = {choose_id}"
    db.exec_sql(sql_ip)
    db.conn.commit()
    return choose_id, re


def write_deliver(user, i):
    assert user in ['wuw', 'hst', 'zhq', 'yqt', 'wcy', 'zzh']
    db = TjdData("task_all")
    db.connect()
    table_name = user
    sql = f"update {table_name} set deliver = 1 where id = {i}"
    db.exec_sql(sql)
    db.conn.commit()


def transfer_available():
    db = TjdData("task_all")
    db.connect()
    table_name = "ava"
    sql = f"select * from {table_name} where idx = 1"
    re = db.exec_sql(sql)
    return bool(re[0][1])


def get_edges_i(username):
    assert username in ['wuw', 'hst', 'zhq', 'yqt', 'wcy', 'zzh']
    db = TjdData("task_all")
    db.connect()
    table_name = username
    sql = f"select * from {table_name} where run = 0"
    re = db.exec_sql(sql)
    if len(re) == 0:
        out = 0
    else:
        choose_ids = [s[0] for s in re]
        out = max(choose_ids)

    # sql =
    return out


def auto_get_user_from_mysql():
    db = TjdData("user_config")
    db.connect()
    table_name = "ip"
    # 获取计算机名称
    hostname = socket.gethostname()
    # 获取本机IP
    ip = socket.gethostbyname(hostname)
    sql = f"select * from {table_name}"
    re = db.exec_sql(sql)
    print("local_ip", ip)
    for v in re:
        if v[1] == ip:
            out = v[2]
        else:
            pass
    assert out in ["wuw", "hst", "zhq", "wcy", "zzh", "yqt"]
    return out


def get_task_name(user):
    assert user in ['wuw', 'hst', 'zhq', 'yqt', 'wcy', 'zzh']
    db = TjdData("task_all")
    db.connect()
    table_name = user
    sql = f"select * from {table_name}"
    re = db.exec_sql(sql)
    names = [r[2] for r in re if bool(r[7])]
    return list(set(names))


def collector_available(user):
    assert user in ['wuw', 'hst', 'zhq', 'yqt', 'wcy', 'zzh']
    db = TjdData("task_all")
    db.connect()
    table_name = user
    sql = f"select * from {table_name}"
    re = db.exec_sql(sql)
    a = 0
    i = 1000
    for s in re:
        if int(s[4]) == 1 and int(s[5]) == 1 and int(s[7]) == 0:
            i = s[0]
            a = 1
            break
    return i, bool(a)


def write_collector(user, i):
    assert user in ['wuw', 'hst', 'zhq', 'yqt', 'wcy', 'zzh']
    db = TjdData("task_all")
    db.connect()
    table_name = user
    sql = f"update {table_name} set collector = 1 where id = {i}"
    db.exec_sql(sql)
    db.conn.commit()


def clear_task(user):
    assert user in ['wuw', 'hst', 'zhq', 'yqt', 'wcy', 'zzh']
    db = TjdData("task_all")
    db.connect()
    table_name = user

    sql = f"delete from {table_name} where id < 10000"
    db.exec_sql(sql)
    db.conn.commit()


def remove_ftp_collector_available(user):
    db = TjdData("task_all")
    db.connect()
    table_name = user
    sql = f"select * from {table_name} where id < 10000"
    re = db.exec_sql(sql)
    db.conn.commit()
    a = True
    for s in re:
        temp_collector_done = bool(s[7])
        a = a and temp_collector_done
    return a


if __name__ == "__main__":
    #user = auto_get_user_from_mysql()
    #i = get_edges_i("yqt")
    # choose_id, re = find_a_task(username="wuw")
    # ----------------upload------------------
    # values = ((1, "PPI_当月同比", 20, 0, 0, "buc"), (1, "PPI_当月同比", 50, 0, 0, "buc"))
    # a = upload_task(values=values, username="hst")
    #re = collector_available("wuw")
    #clear_task("wuw")
    a = get_edges_i("wuw")
    b = remove_ftp_collector_available("wuw")

